package cn.mldn.microboot;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.PropertyResourceBundle;
import java.util.ResourceBundle;

import javax.transaction.Transactional;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.sql.SQLException;

@Transactional
public class DataBaseSql {

	private static ResourceBundle bundle = PropertyResourceBundle.getBundle("db");
	private static Connection conn;
	private static PreparedStatement ps;
	private final static Logger logger = LogManager.getLogger(DataBaseSql.class);

	/* 获取数据库连接的函数 */
	private static Connection getConnection() {

		Connection con = null; // 创建用于连接数据库的Connection对象
		try {
			Class.forName(bundle.getString("db.classname").trim());// 加载Mysql数据驱动
			// com.mysql.jdbc.Driver driver = new com.mysql.jdbc.Driver();
			logger.info("成功加载MySQL驱动程序");
			String url = bundle.getString("db.url").trim();
			String username = bundle.getString("db.username").trim();
			String password = bundle.getString("db.password").trim();
			con = DriverManager.getConnection(url, username, password);// 创建数据连接
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con; // 返回所建立的数据库连接
	}

	/**
	 * 用于注入参数
	 * 
	 * @param ps
	 * @param data
	 * @throws SQLException
	 */
	private static void excutePs(String sql, int length, String[] data) throws SQLException {
		// 预处理SQL 防止注入
		ps = conn.prepareStatement(sql);
		// 注入参数
		for (int i = 0; i < length; i++) {
			ps.setString(i + 1, data[i]);
		}
	}

	/**
	 * 存储返回集
	 * 
	 * @param rs
	 * @param tab_fields
	 * @return
	 * @throws SQLException
	 */
	public static List<Map<String, Object>> save(ResultSet rs, String[] tab_fields) throws SQLException {
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		while (rs.next()) {
			Map<String, Object> map = new LinkedHashMap<String, Object>();
			for (int j = 0; j < tab_fields.length; j++) {
				map.put(tab_fields[j], rs.getString(tab_fields[j]));
			}
			result.add(map);
		}
		return result;
	}

	/**
	 * 创建表
	 * 
	 * @param tabName
	 * @param tab_fields
	 */
	public static void createTable(String tabName, String[] tab_fields) {
		conn = getConnection(); // 首先要获取连接，即连接到数据库
		try {
			String sql = "create table " + tabName + "(id bigint(20) auto_increment primary key not null";
			if (tab_fields != null && tab_fields.length > 0) {
				sql += ",";
				int length = tab_fields.length;
				for (int i = 0; i < length; i++) {
					// 添加字段
					sql += tab_fields[i].trim() + " varchar(50)";
					// 防止最后一个,
					if (i < length - 1) {
						sql += ",";
					}
				}
				// 拼凑完 建表语句 设置默认字符集
				sql += ")ENGINE=InnoDB AUTO_INCREMENT=100000000 DEFAULT CHARSET=utf8;";
				logger.info("建表语句是：" + sql);
				ps = conn.prepareStatement(sql);
				ps.executeUpdate(sql);
				ps.close();
				conn.close(); // 关闭数据库连接
			}
		} catch (SQLException e) {
			logger.error("建表失败" + e.getMessage());
		}
	}

	/**
	 * 添加数据
	 * 
	 * @param tabName
	 * @param fields
	 * @param data
	 */
	public static void insert(String tabName, String[] fields, String[] data) {
		conn = getConnection(); // 首先要获取连接，即连接到数据库
		try {
			String sql = "insert into " + tabName + "(";
			int length = fields.length;
			for (int i = 0; i < length; i++) {
				sql += fields[i];
				// 防止最后一个,
				if (i < length - 1) {
					sql += ",";
				}
			}
			sql += ") values(";
			for (int i = 0; i < length; i++) {
				sql += "?";
				// 防止最后一个,
				if (i < length - 1) {
					sql += ",";
				}
			}
			sql += ");";
			logger.info("添加数据的sql:" + sql);
			// 预处理SQL 防止注入
			excutePs(sql, length, data);
			// 执行
			ps.executeUpdate();
			// 关闭流
			ps.close();
			conn.close(); // 关闭数据库连接
		} catch (SQLException e) {
			System.out.println("添加数据失败" + e.getMessage());
		}
	}

	/**
	 * 查询表 【查询结果的顺序要和数据库字段的顺序一致】
	 * 
	 * @param tabName
	 * @param fields
	 * @param data
	 * @param tab_fields
	 */
	public static List<Map<String, Object>> query(String tabName, String[] fields, String[] data, int page, int size,
			String[] tab_fields) {
		conn = getConnection(); // 首先要获取连接，即连接到数据库
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		try {
			String sql = "select * from  " + tabName + " where ";
			int length = fields.length;
			for (int i = 0; i < length; i++) {
				sql += fields[i] + " = ? ";
				// 防止最后一个,
				if (i < length - 1) {
					sql += " and ";
				}
			}
			sql += " LIMIT ?,? ; ";
			logger.info("查询sql:" + sql);
			// 预处理SQL 防止注入
			// 预处理SQL 防止注入
			ps = conn.prepareStatement(sql);
			// 注入参数
			excutePs(sql, length, data);
			ps.setInt(length + 1, page);
			ps.setInt(length + 2, size);

			// 查询结果集
			ResultSet rs = ps.executeQuery();
			// 存放结果集
			result = save(rs, tab_fields);
			// 关闭流
			rs.close();
			ps.close();
			conn.close(); // 关闭数据库连接
		} catch (SQLException e) {
			logger.error("查询失败" + e.getMessage());
		}
		logger.info("查询结果" + result.toString());
		return result;
	}

	/**
	 * 查询表 【查询结果的顺序要和数据库字段的顺序一致】
	 * 
	 * @param tabName
	 * @param page
	 * @param size
	 * @param tab_fields
	 * @return
	 */
	public static List<Map<String, Object>> query(String tabName, int page, int size, String[] tab_fields) {
		conn = getConnection(); // 首先要获取连接，即连接到数据库
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		try {
			String sql = "select * from  " + tabName + " LIMIT ?,? ; ";
			System.out.println("查询sql:" + sql);
			// 预处理SQL 防止注入
			ps = conn.prepareStatement(sql);
			// 注入参数
			ps.setInt(1, page);
			ps.setInt(2, size);

			// 查询结果集
			ResultSet rs = ps.executeQuery();
			result = save(rs, tab_fields);
			// 关闭流
			rs.close();
			ps.close();
			conn.close(); // 关闭数据库连接
		} catch (SQLException e) {
			System.out.println("查询失败" + e.getMessage());
		}
		return result;
	}

	/**
	 * 清空表数据
	 * 
	 * @param tabName
	 *            表名称
	 */
	public static void delete(String tabName) {
		conn = getConnection(); // 首先要获取连接，即连接到数据库

		try {
			String sql = "delete from  " + tabName + ";";
			System.out.println("删除数据的sql:" + sql);
			// 预处理SQL 防止注入
			ps = conn.prepareStatement(sql);
			// 执行
			ps.executeUpdate();
			// 关闭流
			ps.close();
			conn.close(); // 关闭数据库连接
		} catch (SQLException e) {
			System.out.println("删除数据失败" + e.getMessage());
		}
	}

	/**
	 * 删除数据表 如果执行成功则返回false
	 * 
	 * @param tabName
	 * @return
	 */
	public static boolean dropTable(String tabName) {
		boolean flag = true;
		conn = getConnection(); // 首先要获取连接，即连接到数据库
		try {
			String sql = "drop table  " + tabName + ";";
			// 预处理SQL 防止注入
			ps = conn.prepareStatement(sql);
			// 执行
			flag = ps.execute();
			// 关闭流
			ps.close();
			conn.close(); // 关闭数据库连接
		} catch (SQLException e) {
			System.out.println("删除数据失败" + e.getMessage());
		}
		return flag;
	}
}